#!C:/Perl/bin/perl.exe -w
use CGI;
use DBI;
use strict;

open DEBUG, ">", "debug.txt" or die "Can't open debug log: $!";

my $logFile = "ALEKSlog.txt";

# Strand names and letters, indexed in the same order as the database tables.
my @strands = (
    "Number Sense and Operations",
    "Patterns, Relations, and Algebra",
    "Geometry",
    "Measurement",
    "Data Analysis, Statistics, and Probability"
);
my @strandLetters = ('N', 'P', 'G', 'M', 'D');
my %strandNumber = ('N'=>0, 'P'=>1, 'G'=>2, 'M'=>3, 'D'=>4);

my $styleBlock=<<EOT;
td { padding: 10px }
label { background-color: gray; color: white; padding-left: 2px }
EOT

my $javascript=<<EOT;
function studentSelect() {
    alert('You selected a student!');
}
EOT


# Get the HTTP request, $q, and start a response.
my $q = CGI->new;
print $q->header, $q->start_html(-title=>'ALEKS data', -style=>{'-code'=>$styleBlock}, -script=>$javascript );

# Open the repository (SQLite database).
my $dbfile = 'aleks.sqlite';  # in the current directory
my $db = DBI->connect("dbi:SQLite:dbname=$dbfile","","") or die $DBI::errstr;
my $stmt;  # a temporary SQL statement holder
my @row;  # a temporary row holder

sub menu {
    # Create a popup menu (SELECT element).
    my $label = shift;
    my $selectSQL = shift;
    my @list;
    my @row;
    my $stmt = $db->prepare($selectSQL) or die $db->errstr;
    $stmt->execute() or die $stmt->errstr;
    while (@row = $stmt->fetchrow_array) {
        push @list, $row[0];
    }
    return $q->label($label, $q->popup_menu(-name=>$label . '_selection', -values=>\@list, -onChange=>'this.form.submit()'));
}

sub strandMenu {
    # Create a popup menu for strands.
    my $sql = 'select number, symbol, text from strand;';
    my (@row, @value);
    my $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute() or die $stmt->errstr;
    while (@row = $stmt->fetchrow_array) {
        push @value, $row[1] . ' ' . $row[2];
    }
    return $q->label('strand', $q->popup_menu(-name=>'strand_selection',
                                              -values=>\@value,
                                              -onChange=>'this.form.submit()'));
}

sub standardMenu {
    # Create a popup menu for standards.
    my $sql = 'select grade, strand, number, text from standard;';
    my (@row, @value, %label);
    my ($grade, $strand, $number, $text);
    my $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute() or die $stmt->errstr;
    while (@row = $stmt->fetchrow_array) {
        ($grade, $strand, $number, $text) = @row;
        my $designator = $grade . '.' . $strandLetters[$strand] . '.' . $number;
        push @value, $designator . ' ' . $text;
        $label{$designator . ' ' . $text} = $designator . (' ' x (9 - length($designator))) . substr($text, 0, 70);
    }
    return $q->label('standard', $q->popup_menu(-name=>'standard_selection',
                                                -values=>\@value, 
                                                -labels=>\%label,
                                                -onChange=>'this.form.submit()'));
}

sub listItems {
    my $standard = shift;
    my ($grade, $symbol, $number, @row, @item);
    ($grade, $symbol, $number) = split /\./, $standard, 3;
    $number =~ s/(\d*).*/\1/;
    my $sql = 'select rowid from standard where grade = ? and strand = ? and number = ?;';
    my $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute($grade, $strandNumber{$symbol}, $number) or die $stmt->errstr;
    if (@row = $stmt->fetchrow_array) {
        $standard = $row[0];
    }
    $stmt->finish;
    $sql = 'select text from item where standard = ?';
    $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute($standard) or die $stmt->errstr;
    while (@row = $stmt->fetchrow_array) {
        push @item, $row[0];
    }
    return $q->ul(\@item);
}

# Create a form for data selection.
sub dataSelection {
    my $t;  # text
    $t = $q->start_form(-method=>'GET');

    # List teachers, courses, and students.
    $t .= $q->table($q->Tr([$q->td( [
        menu('teacher', 'select name from teacher;'),
        strandMenu,
        ] ),
        $q->td( [
        menu('course', 'select name from course;'),
        standardMenu,
        ] ),
        $q->td( [
        menu('student', 'select name from student;') 
        ] ),
    ] ) );

    $t .= $q->end_form;
    return $t;
}

print dataSelection;

my $teacher = $q->param('teacher_selection') || ' ';
my $course = $q->param('course_selection') || ' ';
my $student = $q->param('student_selection') || ' ';
my $strand = $q->param('strand_selection') || ' ';
my $standard = $q->param('standard_selection') || ' ';
print $q->p([$teacher, $course, $student, $strand, $standard]);

if ($standard) {
    print listItems($standard);
}

print $q->end_html;

# Disconnect from the database
$db->disconnect;

sub percent {
    # Avoid dividing by zero.
    my $numerator = shift;
    my $denominator = shift;
    if ($denominator == 0) {
        return 0;
    } else {
        return $numerator / $denominator * 100;
    }
}

# Create pie charts.
sub percentPie {
    # E.g., print percentPie(50, 70); would should 50% items mastered in a 70x70 pixel graphic.
    my $percent = shift;
    my $size = shift;
    if (!defined $size) { $size = 70 }
    $size = sprintf('%.0f', $size);
    my $data = sprintf('%.0f', $percent) . ',' . sprintf('%.0f', 100 - $percent);
    my $chartPiece1 = '<img class="pieImg" src="http://chart.apis.google.com/chart?chs=SZxSZ&cht=p&chco=008000&chd=t:';
    my $chartPiece2 = '&chp=4.71" width="SZ" height="SZ" alt="percentage of items mastered" />';
    my $html = $chartPiece1 . $data . $chartPiece2;
    $html =~ s/SZ/$size/g;
    return $html;
}

close DEBUG;
